Introduction to SQLJ

SQLJ and JDBC

Although both SQLJ and JDBC provide Java applications with the capability to access relational databases, there are several differences between them:

• JDBC uses dynamic SQL. In contrast SQLJ can use static SQL. To use static SQL, you run a customization script to bind the SQL statements to the database before you run the application.
• SQLJ requires precompilation. The SQLJ translator converts code that contains SQLJ clauses embedded in Java code into pure Java code that calls into the SQLJ runtime library.

Dynamic SQL versus static SQL

In general, dynamic SQL is more flexible than static SQL, because it does not require SQL statements to be created in advance. Static SQL is more efficient, because the database has done much of the work required to run the SQL statements before run time.

Dynamic SQL provides the ability to query and update tables when, for example, you do not know the number and types of the columns until run time. Using dynamic SQL in JDBC, a Java program can create SQL statements at run time.

With dynamic SQL, database access and authorization are determined at run time. The user of the application must have all required database privileges, and the database must determine the best way to access the required data at run time. However, with static SQL, access and authorization are determined at customization and bind time. The privileges of the user who performs the bind process determine what the application can do. The database determines the best way to access data during customization and bind time, which improves runtime performance.

Many applications do not need to dynamically create SQL statements because the database metadata (such as table and column names) does not change at run time. Such applications can use static SQL, which provides better performance than dynamic SQL.

Advantages of SQLJ

In addition to better performance through using static SQL, SQLJ has the following advantages over JDBC:

• SQLJ generally requires fewer lines of code than JDBC to perform the same tasks, which means less time spent developing, debugging, and maintaining an application.
• The SQLJ translator checks the syntax of SQL statements during translation. This check prevents any runtime SQL syntax errors. JDBC performs no such checks, which can allow runtime SQL syntax errors to occur.
• SQLJ uses database connections to type-check static SQL code. JDBC does not perform type-checking until run time.
• With SQLJ, you can embed Java variables in SQL statements. In contrast, JDBC requires separate get and set calls for each variable, and you must specify the binding by position number.
• SQLJ provides strong typing of query output and return parameters and allows type-checking on calls. JDBC does not perform compile-time type-checking of variables passed to and received from SQL.
• SQLJ provides static package-level security with compile-time encapsulation of database authorization.

JDBC can do one thing that SQLJ cannot do: run dynamic SQL statements. However, if an application needs to use dynamic SQL, you can include some JDBC code in your program with SQLJ clauses.

SQLJ syntax

SQLJ clauses are embedded into Java code. Each clause is preceded by the symbol #sql. The following SQLJ clause is an example of a SELECT statement with a Java variable in the WHERE clause:

#sql [ctx] cursor1 = {SELECT EMP_ACT.EMPNO FROM EMP_ACT WHERE
EMP_ACT.PROJNO = :strProjNo};

SQLJ in DB2

DB2 SQLJ support is based on the SQLJ ANSI standard. The following kinds of SQL constructs can appear in SQLJ programs:

• SELECT statements and expressions
• SQL data manipulation language (DML) statements; for example, INSERT, UPDATE, DELETE
• Data statements; for example, FETCH, SELECT INTO
• Transaction control statements such as COMMIT and ROLLBACK
• Data definition language (DDL) statements such as CREATE, DROP, and ALTER
• Calls to stored procedures and user-defined functions